﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace DataAccess
{
    public class RM
    {
        private string _roomID;
        private string _roomType;
        private double _roomPrice;
        private string _roomStatus;

        #region
        public string roomID
        {
            get { return _roomID; }
            set { _roomID = value; }
        }

        public string roomType
        {
            get { return _roomType; }
            set { _roomType = value; }
        }

        public double roomPrice
        {
            get { return _roomPrice; }
            set { _roomPrice = value; }
        }
        public string roomStatus
        {
            get { return _roomStatus; }
            set { _roomStatus = value; }
        }
        #endregion

        public static int AddRoom(RM rm)
        {
            string sql = "insert into roommanagement(roomID, roomType, roomPrice) values (@roomid,@roomtype,@roomprice)";
            MySqlParameter[] commandParameters = {new MySqlParameter("@roomID",rm._roomID),new MySqlParameter("@roomType",rm._roomType),new MySqlParameter("@roomPrice",rm._roomPrice)};
            int result = MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn,CommandType.Text,sql,commandParameters);
            return result;
        }

        public static DataSet RoomManagementOfQuery(string methods,string input)
        {
            if(methods.Equals("AllRoom")&&input.Equals(""))
            {
                string sql = "select * from roommanagement ";
                MySqlParameter ps = new MySqlParameter("@par1", input);
                DataSet ds = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, sql, ps);
                return ds;
            }
            else
            {
                string sql = "select * from roommanagement where " + methods + "=@par1";
                MySqlParameter ps = new MySqlParameter("@par1", input);
                DataSet ds = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, sql, ps);
                return ds;
            }

            //MySqlParameter ps=new MySqlParameter("@par1",input);
            //DataSet ds = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text,sql , ps);
            //return ds;
        }

        public static bool IsRoomExist(string roomID)
        {
            string sql = "select * from roommanagement where roomId = @roomID";
            Object obj = MySqlHelper.ExecuteScalar(MySqlHelper.Conn, CommandType.Text, sql, new MySqlParameter("@roomId", roomID));
            if (obj == null)
                return false;
            else
                return true;
        }

        public static int DeleteRoom(RM rm)
        {
            string sql = "delete from roommanagement where roomId = @roomID";
            int result = MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn, CommandType.Text, sql, new MySqlParameter("@roomId", rm.roomID));
            return result;

        }
        public static int DeleteRoomStatus(RM rm)
        {
            string sql = "delete from roomstatus where roomId = @roomID";
            int result = MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn, CommandType.Text, sql, new MySqlParameter("@roomId", rm.roomID));
            return result;

        } 

        public static int ModifyRoom(RM rm)
        {
            string sql = "update roommanagement set roomType = @roomtype,roomPrice = @roomprice where roomId = @roomid";
            int result = MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn, CommandType.Text, sql, new MySqlParameter("@roomType", rm._roomType), new MySqlParameter("@roomPrice", rm._roomPrice),new MySqlParameter("@roomId",rm._roomID));
            return result;
        }

        public static object dgv()
        {
            DataView dv = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, "select * from roommanagement").Tables[0].DefaultView;
            return dv;
        }

        public static int updateroom()
        {
            string sql = "update roommanagement rm,roomstatus rs set rs.roomPrice = rm.roomPrice where rs.roomId = rm.roomId";
            int result = MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn, CommandType.Text, sql, null);
            return result;
        }

        public static int AddintoRs(RM rs)
        {
            string sql = "insert into roomstatus(roomID, roomType, roomPrice, roomStatus) values (@roomid,@roomtype,@roomprice,@roomstatus)";
            MySqlParameter[] commandParameters = { new MySqlParameter("@roomID", rs._roomID), new MySqlParameter("@roomType", rs._roomType), new MySqlParameter("@roomPrice", rs._roomPrice), new MySqlParameter("@roomStatus",rs._roomStatus) };
            int result = MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn, CommandType.Text, sql, commandParameters);
            return result;
        }
    }
}
